﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using SEO.Entity;

namespace SEO.Business
{
    /// <summary>
    /// 关键字报表
    /// </summary>
   public class BisKeywordsReport
    {
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <returns></returns>
       public DataTable GetReportList(int nMedicalID, int nSortID,string strKeywords, int nSysLock, int nDiyLock, int nDayIndex, DateTime dBeginTime, DateTime dEndTime, int nBeginCount, int nEndCount, int nPageIndex, int nPageSize, ref int nPageCount, ref int nRecordCount)
        {
            SqlParameter[] parms = new SqlParameter[14];
            parms[0] = new SqlParameter("@MedicalID", nMedicalID);
            parms[1] = new SqlParameter("@SortID", nSortID);
            parms[2] = new SqlParameter("@Keywords", strKeywords);
            parms[3] = new SqlParameter("@SysLock", nSysLock);
            parms[4] = new SqlParameter("@DiyLock", nDiyLock);
            parms[5] = new SqlParameter("@DayIndex", nDayIndex);
            parms[6] = new SqlParameter("@BeginTime", dBeginTime);
            parms[7] = new SqlParameter("@EndTime", dEndTime);
            parms[8] = new SqlParameter("@BeginCount", nBeginCount);
            parms[9] = new SqlParameter("@EndCount", nEndCount);
            parms[10] = new SqlParameter("@PageIndex", nPageIndex);
            parms[11] = new SqlParameter("@PageSize", nPageSize);

            //返回值
            parms[12] = new SqlParameter("@PageCount", nPageCount);
            parms[12].Direction = ParameterDirection.Output;
            parms[13] = new SqlParameter("@RecordCount", nRecordCount);
            parms[13].Direction = ParameterDirection.Output;

            DataTable dt = SqlHelper.ExecuteDataset(
                SqlHelper.database,
                CommandType.StoredProcedure,
                "GetKeywordsReport",
                parms).Tables[0];
            nPageCount = Convert.ToInt32(parms[12].Value);
            nRecordCount = Convert.ToInt32(parms[13].Value);
            return dt;
        }

       /// <summary>
       /// 获取提定天的第一次关键字报表
       /// </summary>
       /// <param name="keywordsIDList"></param>
       /// <param name="dBeginTime"></param>
       /// <param name="dEndTime"></param>
       /// <returns></returns>
       public DataTable GetReportList(string keywordsIDList, DateTime dBeginTime, DateTime dEndTime)
       {
           string strSql = "select [KeywordsID],[MedicalID],[FirstCount],[SecondCount],[ThirdCount] from [KeywordsReport] where [DayIndex]=1 and [KeywordsID] in(" + keywordsIDList + ") and [CreateTime] between @BeginTime and @EndTime";
           List<SqlParameter> parms = new List<SqlParameter>();
           parms.Add(new SqlParameter("@BeginTime", dBeginTime));
           parms.Add(new SqlParameter("@EndTime", dEndTime));
           return SqlHelper.ExecuteDataset(
                            SqlHelper.database,
                            CommandType.Text,
                            strSql,
                            parms.ToArray()).Tables[0];
       }

       /// <summary>
       /// 获取提定天的第一次关键字报表
       /// </summary>
       /// <param name="keywordsIDList"></param>
       /// <param name="dBeginTime"></param>
       /// <param name="dEndTime"></param>
       /// <returns></returns>
       public DataTable GetReportList(DateTime dBeginTime, DateTime dEndTime)
       {
           string strSql = "select [KeywordsID],[MedicalID],[FirstCount],[SecondCount],[ThirdCount] from [KeywordsReport] where [DayIndex]=1 and [CreateTime] between @BeginTime and @EndTime";
           List<SqlParameter> parms = new List<SqlParameter>();
           parms.Add(new SqlParameter("@BeginTime", dBeginTime));
           parms.Add(new SqlParameter("@EndTime", dEndTime));
           return SqlHelper.ExecuteDataset(
                            SqlHelper.database,
                            CommandType.Text,
                            strSql,
                            parms.ToArray()).Tables[0];
       }
 

        /// <summary>
        /// 获取关键字收录情况列表
        /// </summary>
        /// <param name="nMedicalID"></param>
        /// <param name="dBeginTime"></param>
        /// <param name="dEndTime"></param>
        /// <returns></returns>
        public DataTable GetList(int nDayIndex, DateTime dBeginTime, DateTime dEndTime)
        {
            string strSql = @"select kr.[KeywordsID],kr.[MedicalID],krd.[PageIndex],krd.[RowIndex],krd.[AllIndex],w.[WebType],krd.[CreateTime] from [KeysRelation] kr
                            left join [KeysRelationDay] krd on kr.[ID]=krd.[KeysRelationID]
                            left join [WebSEO] w on kr.[WebSEOID]=w.[ID]
                            where krd.[DayIndex]=@DayIndex and krd.[CreateTime] between @BeginTime and @EndTime order by kr.[MedicalID],kr.[KeywordsID],krd.[AllIndex]";
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@DayIndex", nDayIndex));
            parms.Add(new SqlParameter("@BeginTime", dBeginTime));
            parms.Add(new SqlParameter("@EndTime", dEndTime));
            return SqlHelper.ExecuteDataset(
                             SqlHelper.database,
                             CommandType.Text,
                             strSql,
                             parms.ToArray()).Tables[0];
        }

       /// <summary>
       /// 删除报表
       /// </summary>
       /// <param name="nDayIndex"></param>
       /// <param name="dBeginTime"></param>
       /// <param name="dEndTime"></param>
       /// <returns></returns>
        public bool Delete(int nDayIndex, DateTime dBeginTime, DateTime dEndTime)
        {
            string strSql = "delete from [KeywordsReport] where [DayIndex]=@DayIndex and [CreateTime] between @BeginTime and @EndTime";
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@DayIndex", nDayIndex));
            parms.Add(new SqlParameter("@BeginTime", dBeginTime));
            parms.Add(new SqlParameter("@EndTime", dEndTime));
            return SqlHelper.ExecuteNonQuery(SqlHelper.database, CommandType.Text, strSql, parms.ToArray()) > 0;
        }

       /// <summary>
       /// 新增关键字报表
       /// </summary>
       /// <param name="info"></param>
       /// <returns></returns>
        public bool Insert(KeywordsReport Info)
        {
            string strSql = "insert into [KeywordsReport]([KeywordsID],[MedicalID],[DayIndex],[SEOInfo],[FirstCount],[SecondCount],[ThirdCount],[NewsCount],[LinkCount],[CreateTime]) values(@KeywordsID,@MedicalID,@DayIndex,@SEOInfo,@FirstCount,@SecondCount,@ThirdCount,@NewsCount,@LinkCount,@CreateTime)";
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@KeywordsID", Info.KeywordsID));
            parms.Add(new SqlParameter("@MedicalID", Info.MedicalID));
            parms.Add(new SqlParameter("@DayIndex", Info.DayIndex));
            parms.Add(new SqlParameter("@SEOInfo", Info.SEOInfo));
            parms.Add(new SqlParameter("@FirstCount", Info.FirstCount));
            parms.Add(new SqlParameter("@SecondCount", Info.SecondCount));
            parms.Add(new SqlParameter("@ThirdCount", Info.ThirdCount));
            parms.Add(new SqlParameter("@NewsCount", Info.NewsCount));
            parms.Add(new SqlParameter("@LinkCount", Info.LinkCount));
            parms.Add(new SqlParameter("@CreateTime", Info.CreateTime));
            return SqlHelper.ExecuteNonQuery(SqlHelper.database, CommandType.Text, strSql, parms.ToArray()) > 0;
        }

       /// <summary>
       /// 获取关键字报表
       /// </summary>
       /// <param name="?"></param>
       /// <returns></returns>
        public DataTable GetKeysFirstCountList(string strIDList,int nDayIndex, DateTime dBeginTime, DateTime dEndTime)
       {
           string strSql = @"select kr.[KeywordsID],[FirstCount] from [KeywordsReport] kr
                            left join [Medical] m on kr.[MedicalID]=m.[ID]
                            where m.[Status]=1 and kr.[DayIndex]=@DayIndex and kr.[KeywordsID] in(" + strIDList + ") and kr.[CreateTime] between @BeginTime and @EndTime order by kr.[KeywordsID] desc";
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@DayIndex", nDayIndex));
            parms.Add(new SqlParameter("@BeginTime", dBeginTime));
            parms.Add(new SqlParameter("@EndTime", dEndTime));
            return SqlHelper.ExecuteDataset(
                             SqlHelper.database,
                             CommandType.Text,
                             strSql,
                             parms.ToArray()).Tables[0];
       }
    }
}
